package com.test.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.test.bean.Category;
import com.test.util.SQLiteUtils;

public class CategoryDAO {
	/**
	 * 通过ＩＤ查询
	 * @param id
	 */
	public static Category selectById(Integer id) {
		Connection conn = SQLiteUtils.getConnection();
		Category category = new Category();
		try {
			Statement stat = conn.createStatement();
			conn.setAutoCommit(false);
			ResultSet rs = stat.executeQuery("select * from t_category where id=" + id);
			
			if (rs != null && rs.next()) {
				category.setId(rs.getInt("id"));
				category.setName(rs.getString("name"));
				category.setPid(rs.getInt("pid"));
				category.setOrderNum(rs.getInt("order_num"));
			}
			conn.commit();
			conn.close();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				throw new RuntimeException(e1);
			}
			throw new RuntimeException(e);
		}
		
		return category;
	}
	
	
	/**
	 * 通过name和pid查询
	 * @param id
	 */
	public static Category selectByNameAndPid(String name, Integer pid) {
		Connection conn = SQLiteUtils.getConnection();
		Category category = null;
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery("select * from t_category where lower(name)='" + name.toLowerCase() + "' and pid="+ pid);
			
			if (rs != null && rs.next()) {
				category = new Category();
				category.setId(rs.getInt("id"));
				category.setName(rs.getString("name"));
				category.setPid(rs.getInt("pid"));
				category.setOrderNum(rs.getInt("order_num"));
			} else {
				return null;
			}
			conn.close();
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		
		return category;
	}
	
	
	/**
	 * insert
	 * @param category
	 */
	public static void insert(Category category) {
		Connection conn = SQLiteUtils.getConnection();
		try {
			PreparedStatement stat = conn.prepareStatement("insert into t_category(name, pid, order_num) values(?,?,?)", Statement.RETURN_GENERATED_KEYS);
			conn.setAutoCommit(false);
			
			stat.setString(1, category.getName());
			stat.setInt(2, category.getPid());
			stat.setInt(3, category.getOrderNum());
			
			stat.executeUpdate();
			ResultSet rs = stat.getGeneratedKeys();
			if (rs != null && rs.next()) {
				category.setId(rs.getInt(1));
			}
			
			conn.commit();
			conn.close();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				throw new RuntimeException(e1);
			}
			throw new RuntimeException(e);
		}
	}
	
	
	/**
	 * update
	 * @param category
	 */
	public static void update(Category category) {
		Connection conn = SQLiteUtils.getConnection();
		try {
			PreparedStatement stat = conn.prepareStatement("update t_category set name=?, pid=?, order_num=? where id=?");
			conn.setAutoCommit(false);
			
			stat.setString(1, category.getName());
			stat.setInt(2, category.getPid());
			stat.setInt(3, category.getOrderNum());
			stat.setInt(4, category.getId());
			
			stat.executeUpdate();
			
			conn.commit();
			conn.close();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				throw new RuntimeException(e1);
			}
			throw new RuntimeException(e);
		}
	}
	
	/**
	 * 取所有
	 * @return
	 */
	public static List<Category> selectAll() {
		List<Category> list = new ArrayList<Category>();
		
		Connection conn = SQLiteUtils.getConnection();
		ResultSet rs = null;
		try {
			Statement stat = conn.createStatement();
			rs = stat.executeQuery("select * from t_category order by order_num");
			if (rs != null) {
				while (rs.next()) {
					Category category = new Category();
					category.setId(rs.getInt("id"));
					category.setName(rs.getString("name"));
					category.setPid(rs.getInt("pid"));
					category.setOrderNum(rs.getInt("order_num"));
					
					list.add(category);
				}
			}
			
			conn.close();
		} catch (Exception e) {
		}
		return list;
	} 
	
	
	/**
	 * delete
	 * 没有文章,没有子节点的才能删除
	 * @param category
	 */
	public static int delete(Integer id) {
		Connection conn = SQLiteUtils.getConnection();
		try {
			PreparedStatement stat = conn.prepareStatement("delete from t_category  where id=? and " +
					"not exists(select 1 from t_category t2 where t2.pid=?) and " +
					"not exists(select 1 from t_article where category_id=?)");
			
			conn.setAutoCommit(false);
			
			stat.setInt(1, id);
			stat.setInt(2, id);
			stat.setInt(3, id);
			
			int row = stat.executeUpdate();
			
			conn.commit();
			conn.close();
			return row;
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				throw new RuntimeException(e1);
			}
			throw new RuntimeException(e);
		}
	}
}
